from datetime import datetime

from sqlalchemy import func, select, exc, and_, or_

from app.config.db import DBUtils
from app.model.PfpjModel import PfpjModel
from app.model.companyScoreModel import CompanyScore
from app.model.BusinessInfoModel import BusinessInfo
from app.model.CompanyPositionModel import CompanyPositionModel
from app.model.FKListModel import fklist
from app.model.RiskHistoryModel import RiskHistory
from app.model.RiskTitlelModel import RiskTitle
from app.model.SKListModel import sklist


class UserPortraitService:
    def __init__(self):
        self.db_utils = DBUtils()
        self.current_year = datetime.now().year
        self.fk_year = fklist.JYND == int(self.current_year)
        self.sk_year = sklist.JYND == int(self.current_year)
        self.fk_dif_name = fklist.JS_YWFL != '同名户结算'  # 付款表排除同名户
        self.sk_dif_name = sklist.JS_YWFL != '同名户结算'  # 收款表排除同名户
        # print(self.current_year)

    # 查询董监高职位
    def select_user_position(self, session, company):
        # 查询全部
        try:
            position_list = session.query(CompanyPositionModel).filter(CompanyPositionModel.GSMC == company).all()
            if position_list:
                return [item.to_all_dict() for item in position_list]
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 查看风险标签
    def select_company_fx_label(self, session, company, year):
        try:
            query = (
                select(
                    RiskTitle.FXLX,
                    func.sum(RiskTitle.FKJE).label('total_amount')
                )
                .where(
                    and_(RiskTitle.GSMC == company, RiskTitle.YEAR == str(year))
                )
                .group_by(RiskTitle.FXLX)
            )
            results = session.execute(query).all()
            # 将结果转换为字典列表，确保 JSON 可序列化
            result_dicts = [
                {
                    "TYPE": item.FXLX,
                    "totalamount": round(float(item.total_amount) / 10000, 2) if item.total_amount is not None else 0
                }
                for item in results
            ]
            if result_dicts:
                return result_dicts
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 查看工商信息
    def select_company_info(self, session, company):
        try:
            result = session.query(BusinessInfo).filter(BusinessInfo.GSMC == company) \
                .order_by(BusinessInfo.HZRQ.desc()).first()
            if result:
                return [result.to_dict()]
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 上游单位TOP5
    def select_upstream_info(self, session, company, is_cydw):
        if int(is_cydw) in range(1, 4):
            if int(is_cydw) == 1:
                fk_gsmc = fklist.FKZHMC == company
            elif int(is_cydw) == 2:
                fk_gsmc = fklist.FKGS == company
            elif int(is_cydw) == 3:
                fk_gsmc = 1 == 1
            try:
                upstream_list = session.query(fklist.SKZHMC,
                                              func.sum(fklist.FKJE).label('total')) \
                    .filter(and_(fk_gsmc, self.fk_dif_name, self.fk_year)) \
                    .group_by(fklist.SKZHMC) \
                    .order_by(func.sum(fklist.FKJE).desc()) \
                    .limit(5).all()
                if upstream_list:
                    return [{
                        'gsmc': item[0],
                        'total': round(item[1] / 10000, 2) if item[1] >= 50 or item[1] == 0 else 0.01
                    } for item in upstream_list]
                else:
                    return []
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
        else:
            try:
                upstream_list = session.query(sklist.SKZHMC,
                                              func.sum(sklist.FKJE).label('total')) \
                    .filter(and_(sklist.FKZHMC == company, self.sk_dif_name, self.sk_year)) \
                    .group_by(sklist.SKZHMC) \
                    .order_by(func.sum(sklist.FKJE).desc()) \
                    .limit(5).all()

                if upstream_list:
                    return [{
                        'gsmc': item[0],
                        'total': round(item[1] / 10000, 2) if item[1] >= 50 or item[1] == 0 else 0.01
                    } for item in upstream_list]
                else:
                    return []
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")

    # 下游单位TOP5
    def select_downstream_info(self, session, company, is_cydw):
        if int(is_cydw) in range(1, 4):
            if int(is_cydw) == 1:
                sk_gsmc = sklist.SKZHMC == company
            elif int(is_cydw) == 2:
                sk_gsmc = sklist.SKGS == company
            elif int(is_cydw) == 3:
                sk_gsmc = 1 == 1
            try:
                downstream_list = session.query(sklist.FKZHMC,
                                                func.sum(sklist.FKJE).label('total')) \
                    .filter(and_(sk_gsmc, self.sk_dif_name, self.sk_year)) \
                    .group_by(sklist.FKZHMC) \
                    .order_by(func.sum(sklist.FKJE).desc()) \
                    .limit(5).all()

                if downstream_list:
                    return [{
                        'gsmc': item[0],
                        'total': round(item[1] / 10000, 2) if item[1] >= 50 or item[1] == 0 else 0.01
                    } for item in downstream_list]
                else:
                    return []
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")
        else:
            try:
                downstream_list = session.query(fklist.FKZHMC,
                                                func.sum(fklist.FKJE).label('total')) \
                    .filter(and_(fklist.SKZHMC == company, self.fk_dif_name, self.fk_year)) \
                    .group_by(fklist.FKZHMC) \
                    .order_by(func.sum(fklist.FKJE).desc()) \
                    .limit(5).all()
                if downstream_list:
                    return [{
                        'gsmc': item[0],
                        'total': round(item[1] / 10000, 2) if item[1] >= 50 or item[1] == 0 else 0.01
                    } for item in downstream_list]
                else:
                    return []
            except exc.SQLAlchemyError as e:
                error_response = [{'error': str(e)}]
                try:
                    self.db_utils.rollback()
                except Exception as rollback_error:
                    print(f"Failed to rollback transaction: {rollback_error}")
                return error_response
            finally:
                try:
                    self.db_utils.remove_session()
                except Exception as remove_error:
                    print(f"Failed to rollback transaction: {remove_error}")

    # 查询风险历史信息
    def select_risk_history(self, session, company):
        # ,
        # func.to_char(RiskHistory.SCRQ,
        #              'YYYY') == self.current_year   # 限制年份
        try:
            risk_list = session.query(RiskHistory.TGSJ,
                                      RiskHistory.JYJE,
                                      RiskHistory.SXLX).filter(or_(RiskHistory.JYDS == company, RiskHistory.CYDW == company) ) \
                .order_by(RiskHistory.TGSJ.desc()).limit(30).all()

            if risk_list:
                return [{'date': item[0],
                         'fkje': round(item[1]*10000 / 10000, 2) if item[1]*10000 >= 50 or item[1]*10000 == 0 else 0.01,
                         'type': item[2]
                         } for item in risk_list]
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

    # 企业评分
    def select_score_info(self, session, company):
        try:
            Co_score = session.execute(
                select(PfpjModel.END_SCORE).where(PfpjModel.CUST_NAME == company).order_by(PfpjModel.CHECK_DATE.desc())
            ).scalars().first()
            if Co_score is not None:
                return [{
                    'composite_score': Co_score,
                    'bank_score': Co_score
                }]
            else:
                return []
        except exc.SQLAlchemyError as e:
            error_response = [{'error': str(e)}]
            try:
                self.db_utils.rollback()
            except Exception as rollback_error:
                print(f"Failed to rollback transaction: {rollback_error}")
            return error_response
        finally:
            try:
                self.db_utils.remove_session()
            except Exception as remove_error:
                print(f"Failed to rollback transaction: {remove_error}")

            # bank_query = select(BankScore).where(BankScore.GSMC == company)
            # bank_list = session.execute(bank_query).scalars().all()
            # bank_result = [item.to_all_dict() for item in bank_list]
            # supplier_result = 80
            # credit_result = 80
            # bank_weight = 0.6
            # supplier_weight = 0.2
            # credit_weight = 0.2
            # if bank_result and supplier_result and credit_result:
            #     bank_param = int(bank_result[0]['ZF'])
            #     bank_score = (bank_param / 700) * 100
            #     bank_weight_score = bank_score * bank_weight
            #     supplier_weight_score = supplier_result * supplier_weight
            #     credit_weight_score = credit_result * credit_weight
            #     composite_score = bank_weight_score + supplier_weight_score + credit_weight_score
            # else:
            #     bank_score = 80
            #     bank_weight_score = bank_score * bank_weight
            #     supplier_weight_score = supplier_result * supplier_weight
            #     credit_weight_score = credit_result * credit_weight
            #     composite_score = bank_weight_score + supplier_weight_score + credit_weight_score
            #     return [{
            #         'composite_score': composite_score,
            #         'bank_score': bank_score,
            #         'supplier_score': supplier_result,
            #         'credit_score': credit_result,
            #     }]
            # if bank_score and composite_score:
            #     return [{
            #         'composite_score': composite_score,
            #         'bank_score': bank_score,
            #         'supplier_score': supplier_result,
            #         'credit_score': credit_result,
            #     }]
            # else:
            #     return []
